﻿using System;
using System.Data;
using System.Text;
using System.Collections.Generic;
using System.Data.OracleClient;
namespace GSLMS.DAL.Oracle
{
	/// <summary>
	/// 数据访问类:SYS_METINFOSETMAIN
	/// </summary>
	public partial class SYS_METINFOSETMAIN : BaseDAL<GSLMS.Entity.TB_SYS_METINFOSETMAIN>
	{
		private readonly GSLMS.Core.Loger log = new Core.Loger();
		public SYS_METINFOSETMAIN()
		{}
		#region  Method
		/// <summary>
		///  增加一条数据
		/// </summary>
		public override bool Add(GSLMS.Entity.TB_SYS_METINFOSETMAIN model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_METINFONAME", model.METINFONAME),
								new OracleParameter("P_SYSTEMID", model.SYSTEMID),
								new OracleParameter("P_CREATEBY", model.CREATEBY),
								new OracleParameter("P_CREATEDATE", model.CREATEDATE),
                                new OracleParameter("returnvalue",OracleType.Number)
            };
  
			for (int i = 0; i < 5; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

            parms[5].Direction = ParameterDirection.Output;

			try
			{
                OracleConnection con = new OracleConnection(OracleHelper.connectionString);
                con.Open();
                OracleTransaction tran = con.BeginTransaction();
                OracleCommand cmd = con.CreateCommand();
                cmd.Transaction = tran;

                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(parms);
                cmd.CommandType=CommandType.StoredProcedure;
                cmd.CommandText="pkg_sys.proc_METINFOSETMAIN_add";
                
                cmd.ExecuteNonQuery();
                long num=0;

                if(parms[5].Value!=DBNull.Value)
                    num=OracleHelper.ToLong(parms[5].Value.ToString());
                if(num>0)
                {
                  flag=AddDetail(ref cmd,model.DetailList,num);
                  if(flag)
                  {
                    tran.Commit();
                    //清除缓存
                    OracleHelper.RemoveCacheObject("_MetinfoSetMainList");
                  }
                  else
                  {
                    tran.Rollback();
                  }

                }
                else
                {
                  tran.Rollback();
                }

               

                cmd.Dispose();
                tran.Dispose();
                con.Close();
                con.Dispose();
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

        private bool AddDetail(ref OracleCommand cmd,List<GSLMS.Entity.TB_SYS_METINFOSETDETAIL> list,long pid)
        {
            bool flag = false;
            if (list != null && list.Count > 0)
            {
                foreach (var model in list)
                {
                    OracleParameter[] parms = new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_METIINFOID", pid),
								new OracleParameter("P_SORTNO", model.SORTNO),
								new OracleParameter("P_INFONAME", model.INFONAME),
								new OracleParameter("P_INFOREMARK", model.INFOREMARK)};

                    for (int i = 0; i < 5; i++)
                    {
                        parms[i].Direction = ParameterDirection.Input;
                    }

                    cmd.Parameters.Clear();
                    cmd.CommandType = CommandType.StoredProcedure;
                    cmd.CommandText = "pkg_sys.proc_METINFOSETDETAIL_add";
                    cmd.Parameters.AddRange(parms);

                    try
                    {
                        int num = cmd.ExecuteNonQuery();
                        if (num != 1)
                        {
                            flag = false;
                            break;
                        }
                    }
                    catch (Exception ex)
                    {
                        log.ExceptionHand(ex);
                    }

                
                }
                flag = true;
            }
            else
            {
                flag = true;
            }


            return flag;
        }
             

		/// <summary>
		///  更新一条数据
		/// </summary>
		public override bool Update(GSLMS.Entity.TB_SYS_METINFOSETMAIN model)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", model.ID),
								new OracleParameter("P_METINFONAME", model.METINFONAME),
								new OracleParameter("P_SYSTEMID", model.SYSTEMID),
								new OracleParameter("P_CREATEBY", model.CREATEBY),
								new OracleParameter("P_CREATEDATE", model.CREATEDATE)};
  
			for (int i = 0; i < 5; i++)
			{
				parms[i].Direction = ParameterDirection.Input;
			}

			try
			{
                OracleConnection con = new OracleConnection(OracleHelper.connectionString);
                con.Open();
                OracleTransaction tran = con.BeginTransaction();
                OracleCommand cmd = con.CreateCommand();
                cmd.Transaction = tran;
                

                cmd.Parameters.Clear();
                cmd.Parameters.AddRange(parms);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.CommandText = "pkg_sys.proc_METINFOSETMAIN_update";
               
              int num=cmd.ExecuteNonQuery();
               
                if (num > 0)
                {
                    flag = AddDetail(ref cmd, model.DetailList, model.ID);
                    if (flag)
                    {
                        tran.Commit();
                        //清除缓存
                        OracleHelper.RemoveCacheObject("_MetinfoSetMainList");
                    }
                    else
                    {
                        tran.Rollback();
                    }

                }
                else
                {
                    tran.Rollback();
                }



                cmd.Dispose();
                tran.Dispose();
                con.Close();
                con.Dispose();

               
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///  删除一条数据
		/// </summary>
		public override bool Delete(long ID)
		{
			bool flag = false;
			OracleParameter[] parms =new OracleParameter[]{
								new OracleParameter("P_ID", ID)};
  
				parms[0].Direction = ParameterDirection.Input;

			try
			{
                OracleHelper.ExecuteNonQuery(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_METINFOSETMAIN_delete", parms);
				flag = true;
                //清除缓存
                OracleHelper.RemoveCacheObject("_MetinfoSetMainList");
			}
			catch (Exception ex)
			{
				log.ExceptionHand(ex);
			}
			return flag;
		}

		/// <summary>
		///DataRowToModel 
		/// </summary>
		private GSLMS.Entity.TB_SYS_METINFOSETMAIN DataRowToModel(DataRow dr)
		{
			if (dr != null && dr.ItemArray.Length > 0)
			{
				GSLMS.Entity.TB_SYS_METINFOSETMAIN model = new GSLMS.Entity.TB_SYS_METINFOSETMAIN();
				if(dr["ID"]!=null && dr["ID"].ToString()!="")
				{
					model.ID=OracleHelper.ToLong(dr["ID"].ToString());
				}
				if(dr["METINFONAME"]!=null && dr["METINFONAME"].ToString()!="")
				{
					model.METINFONAME=dr["METINFONAME"].ToString();
				}
				if(dr["SYSTEMID"]!=null && dr["SYSTEMID"].ToString()!="")
				{
					model.SYSTEMID=OracleHelper.ToLong(dr["SYSTEMID"].ToString());
				}
				if(dr["CREATEBY"]!=null && dr["CREATEBY"].ToString()!="")
				{
					model.CREATEBY=dr["CREATEBY"].ToString();
				}
				if(dr["CREATEDATE"]!=null && dr["CREATEDATE"].ToString()!="")
				{
					model.CREATEDATE=DateTime.Parse(dr["CREATEDATE"].ToString());
				}
				return model;
			}
			else
			{
				return null;
			}
		}

        /// <summary>
        /// 从缓存中获取材料清单设置信息
        /// </summary>
        /// <returns></returns>
        private DataTable GetAllByCache()
        {
            DataTable dt = null;
            if (OracleHelper.GetCachecObject("_MetinfoSetMainList") != null)
                dt = (DataTable)OracleHelper.GetCachecObject("_MetinfoSetMainList");
            if (dt == null || dt.Rows.Count == 0)
            {
                string sql = "";
                sql = "select * from TB_SYS_METINFOSETMAIN order by id desc";
                DataSet ds = OracleHelper.ExecuteDataset(sql);
                if (ds != null && ds.Tables.Count > 0)
                {
                    dt = ds.Tables[0];
                    OracleHelper.AddCacheObject("_MetinfoSetMainList", (object)dt, 0);
                    //清除缓存
                    //OracleHelper.RemoveCacheObject("_MetinfoSetMainList");
                }

            }

            return dt;
        }

        /// <summary>
        /// 根据系统ID提取材料设置信息
        /// </summary>
        /// <param name="systemID">当为小于等于0时提取所有的1典当，2信贷，3担保</param>
        /// <returns></returns>
        public List<GSLMS.Entity.TB_SYS_METINFOSETMAIN> GetMetInfoSetMianList(int systemID)
        {
            var list = new List<GSLMS.Entity.TB_SYS_METINFOSETMAIN>();
            DataTable dt = null;
            string sqlWhere = "";
            if (systemID > 0)
                sqlWhere = " systemid="+systemID.ToString();

            dt = OracleHelper.DataTableFileter(GetAllByCache(), sqlWhere, " id desc");
           
            if (dt != null && dt.Rows.Count > 0)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    GSLMS.Entity.TB_SYS_METINFOSETMAIN model = new GSLMS.Entity.TB_SYS_METINFOSETMAIN();
                    model = DataRowToModel(dt.Rows[i]);
                    list.Add(model);
                }
            }

            return list;
        }

		/// <summary>
		/// 得到一个对象实体
		/// </summary>
		public override GSLMS.Entity.TB_SYS_METINFOSETMAIN GetByID(long ID)
		{
			GSLMS.Entity.TB_SYS_METINFOSETMAIN sModel = new GSLMS.Entity.TB_SYS_METINFOSETMAIN();
            OracleParameter[] parms = new OracleParameter[] { 
               new OracleParameter("P_ID",ID),
               new OracleParameter("p_cur",OracleType.Cursor),
               new OracleParameter("p_dcur",OracleType.Cursor)
            };

            parms[0].Direction = ParameterDirection.Input;
            parms[1].Direction = ParameterDirection.Output;
            parms[2].Direction = ParameterDirection.Output;

            DataSet ds = OracleHelper.ExecuteDataset(OracleHelper.connectionString, CommandType.StoredProcedure, "pkg_sys.proc_METINFOSETMAIN_get", parms);

			if (ds != null && ds.Tables.Count > 0 && ds.Tables[0].Rows.Count > 0)
			{
				sModel = DataRowToModel(ds.Tables[0].Rows[0]);
                if (ds.Tables.Count > 1)
                {
                    var dList = new List<GSLMS.Entity.TB_SYS_METINFOSETDETAIL>();
                    DataTable dDt = ds.Tables[1];
                    for (int i = 0; i < dDt.Rows.Count; i++)
                    {
                        GSLMS.Entity.TB_SYS_METINFOSETDETAIL dmodel = new GSLMS.Entity.TB_SYS_METINFOSETDETAIL();
                        dmodel = DataRowToDetailModel(dDt.Rows[i]);
                        dList.Add(dmodel);
                    }

                    sModel.DetailList = dList;
                }
                   
			}
			else
			{
			return null;
			}
			return sModel;
		}

        /// <summary>
        ///DataRowToModel 
        /// </summary>
        private GSLMS.Entity.TB_SYS_METINFOSETDETAIL DataRowToDetailModel(DataRow dr)
        {
            if (dr != null && dr.ItemArray.Length > 0)
            {
                GSLMS.Entity.TB_SYS_METINFOSETDETAIL model = new GSLMS.Entity.TB_SYS_METINFOSETDETAIL();
                if (dr["ID"] != null && dr["ID"].ToString() != "")
                {
                    model.ID = OracleHelper.ToLong(dr["ID"].ToString());
                }
                if (dr["METIINFOID"] != null && dr["METIINFOID"].ToString() != "")
                {
                    model.METIINFOID = OracleHelper.ToLong(dr["METIINFOID"].ToString());
                }
                if (dr["SORTNO"] != null && dr["SORTNO"].ToString() != "")
                {
                    model.SORTNO = OracleHelper.ToInt(dr["SORTNO"].ToString());
                }
                if (dr["INFONAME"] != null && dr["INFONAME"].ToString() != "")
                {
                    model.INFONAME = dr["INFONAME"].ToString();
                }
                if (dr["INFOREMARK"] != null && dr["INFOREMARK"].ToString() != "")
                {
                    model.INFOREMARK = dr["INFOREMARK"].ToString();
                }
                return model;
            }
            else
            {
                return null;
            }
        }


		/// <summary>
		/// 分页提取数据
		/// </summary>
		public override List<GSLMS.Entity.TB_SYS_METINFOSETMAIN> GetList(GSLMS.Entity.TB_SYS_METINFOSETMAIN searchParam)
		{
			var list=new List<GSLMS.Entity.TB_SYS_METINFOSETMAIN>();
			DataTable dt = null;
			string tblName = "TB_SYS_METINFOSETMAIN";
			string sqlWhere="";
			string strSort="";
			int pageSize=-1;
			int pageIndex=1;
			string strFields="*";
			int totalCount = 0;
			int countFlag = 1;

            if(searchParam.METINFONAME!=null&&searchParam.METINFONAME.Trim()!="")
                sqlWhere+=" and METINFONAME like '%"+OracleHelper.ReplaceUnsafeSql(searchParam.METINFONAME.Trim())+"%'";
            if (searchParam.SYSTEMID != null && searchParam.SYSTEMID > 0)
                sqlWhere += " and SYSTEMID=" + searchParam.SYSTEMID.ToString();

			dt = OracleHelper.GetPageData(tblName, strFields, strSort, pageSize, pageIndex,countFlag, sqlWhere, out totalCount);
			if (dt != null && dt.Rows.Count > 0)
			{
				for (int i = 0; i < dt.Rows.Count; i++)
				{
					GSLMS.Entity.TB_SYS_METINFOSETMAIN model = new GSLMS.Entity.TB_SYS_METINFOSETMAIN();
					model = DataRowToModel(dt.Rows[i]);
					list.Add(model);
				}
			}
			
			return list;
		}

		#endregion  Method
	}
}

